import java.awt.Color;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.BorderFactory;
import javax.swing.JButton;
import javax.swing.JDesktopPane;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import net.proteanit.sql.DbUtils;


/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author Bidya
 */
public class ChainRegistration {

    String date = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
    final DateFormat timeFormat = new SimpleDateFormat("HH : mm : ss");
    JFrame frame = new JFrame("Registration Form");
    JDesktopPane pane = new JDesktopPane();
    JPanel p0 = new JPanel();
    JLabel lbl0 = new JLabel();
    JLabel lbl1 = new JLabel();
    JLabel lbl2 = new JLabel();
    JLabel lbl3 = new JLabel();
    JLabel lbl4 = new JLabel();
    JLabel lbname = new JLabel();
    JLabel lbid = new JLabel();
    JLabel lbph = new JLabel();
    JLabel lbadd = new JLabel();
    JLabel lbadd1 = new JLabel();
    JLabel lbpin = new JLabel();
    JLabel lbgift = new JLabel();
     JLabel lbveh = new JLabel();
    JLabel lbdate = new JLabel();
    JLabel lbtime = new JLabel();
    JTextField nametxt = new JTextField();
    JTextField idtxt = new JTextField();
    JTextField phtxt = new JTextField();
    JTextField gifttxt = new JTextField();
    JTextField vehtxt = new JTextField();
    JTextField satettxt = new JTextField();
    JTextField pintxt = new JTextField();
     JTextField citytxt = new JTextField();
    JButton save = new JButton();
    JButton edit = new JButton();
    JButton exit = new JButton();
    JButton update = new JButton();
    JButton clear = new JButton();
    JButton view = new JButton();
    JButton clint=new JButton();
    JScrollPane scp = new JScrollPane();
    JTable table = new JTable();

    ChainRegistration() {
        frame.setLocation(150, 30);
        frame.setVisible(true);
        frame.setSize(900, 600);
        frame.setResizable(false);
        frame.add(pane);
        pane.setBounds(0, 0, 900, 600);
        pane.setBackground(Color.LIGHT_GRAY);
        pane.add(lbl0);
        lbl0.setBounds(250, 10, 500, 40);
        lbl0.setText("Registration Form");
        lbl0.setForeground(Color.red);
        lbl0.setFont(new Font("Algerian", Font.BOLD, 40));
        lbl0.setHorizontalAlignment(JLabel.CENTER);
        pane.add(lbdate);
        lbdate.setBounds(20, 32, 55, 20);
        lbdate.setText("Date:-");
        lbdate.setForeground(Color.blue);
        lbdate.setFont(new Font("Bell MT", Font.ITALIC, 18));
        
        pane.add(lbl1);
        lbl1.setBounds(70, 30, 100, 30);
        lbl1.setFont(new Font("Bell MT", Font.ITALIC, 18));
        lbl1.setHorizontalAlignment(JLabel.LEFT);
        lbl1.setForeground(Color.BLUE);
        lbl1.setText("" + date);

        pane.add(lbl2);
        lbl2.setBounds(20, 50, 150, 30);
        lbl2.setFont(new Font("Bell MT", Font.ITALIC, 18));
        lbl2.setHorizontalAlignment(JLabel.LEFT);
        final Date date = new Date();
        String time = timeFormat.format(date);
        lbl2.setText("Time - " + time);
        lbl2.setForeground(Color.BLUE);
        
        //---registration-------
        pane.add(lbid);
        lbid.setBounds(20, 100, 60, 30);
        lbid.setText("Reg. ID");
        lbid.setFont(new Font("Berlin Sans FB",Font.HANGING_BASELINE,20));
        lbid.setForeground(Color.BLUE);
        pane.add(idtxt);
        idtxt.setBounds(130, 100, 150,30);
        
         //---Name-------
        pane.add(lbname);
        lbname.setBounds(20, 140, 60, 30);
        lbname.setText("Name");
        lbname.setFont(new Font("Berlin Sans FB",Font.HANGING_BASELINE,20));
        lbname.setForeground(Color.BLUE);
        pane.add(nametxt);
        nametxt.setBounds(130, 140, 150,30);
        
         //---city-------
        pane.add(lbadd);
        lbadd.setBounds(20, 180, 60, 30);
        lbadd.setText("City");
        lbadd.setFont(new Font("Berlin Sans FB",Font.HANGING_BASELINE,20));
        lbadd.setForeground(Color.BLUE);
        pane.add(citytxt);
        citytxt.setBounds(130, 180, 150,30);
        
         //---State-------
        pane.add(lbadd1);
        lbadd1.setBounds(20, 220, 60, 30);
        lbadd1.setText("State");
        lbadd1.setFont(new Font("Berlin Sans FB",Font.HANGING_BASELINE,20));
        lbadd1.setForeground(Color.BLUE);
        pane.add(satettxt);
        satettxt.setBounds(130, 220, 150,30);
        
         //---pin-------
        pane.add(lbpin);
        lbpin.setBounds(20, 260, 60, 30);
        lbpin.setText("Pin.");
        lbpin.setFont(new Font("Berlin Sans FB",Font.HANGING_BASELINE,20));
        lbpin.setForeground(Color.BLUE);
        pane.add(pintxt);
        pintxt.setBounds(130, 260, 150,30);
        
         //--Contact-------
         pane.add(lbph);
        lbph.setBounds(20, 300, 100, 30);
        lbph.setText("Contact No.");
        lbph.setFont(new Font("Berlin Sans FB",Font.HANGING_BASELINE,20));
        lbph.setForeground(Color.BLUE);
        pane.add(phtxt);
        phtxt.setBounds(130, 300, 150,30);
        
         //--vehicle regstaration-------
        pane.add(lbveh);
        lbveh.setBounds(20, 340, 100, 30);
        lbveh.setText("Vehicle Reg.");
        lbveh.setFont(new Font("Berlin Sans FB",Font.HANGING_BASELINE,20));
        lbveh.setForeground(Color.BLUE);
        pane.add(vehtxt);
        vehtxt.setBounds(130, 340, 150,30);
        
         //--gift-------
        pane.add(lbgift);
        lbgift.setBounds(20, 380, 60, 30);
        lbgift.setText("Gift");
        lbgift.setFont(new Font("Berlin Sans FB",Font.HANGING_BASELINE,20));
        lbgift.setForeground(Color.BLUE);
        pane.add(gifttxt);
        gifttxt.setBounds(130, 380, 150,30);
        
        pane.add(p0);
        p0.setBounds(20, 430, 850, 80);
        p0.setBackground(Color.LIGHT_GRAY);
        p0.setLayout(null);
        p0.setBorder(BorderFactory.createTitledBorder(null, "  Controls  ",
				2, 2, null, Color.red));
        //-----Buttons------
        p0.add(save);
        save.setBounds(10, 25, 90, 40);
        save.setText("Save");
        
        p0.add(edit);
        edit.setBounds(110,25, 100, 40);
        edit.setText("Edit");
        
        p0.add(clear);
        clear.setBounds(220,25, 100, 40);
        clear.setText("Clear");
        
        p0.add(exit);
        exit.setBounds(330,25, 100, 40);
        exit.setText("Reset");
        
        p0.add(view);
        view.setBounds(440,25, 100, 40);
        view.setText("View");
        
        p0.add(clint);
        clint.setBounds(550,25, 100, 40);
        clint.setText("Clint_info");
     
        p0.add(update);
        update.setBounds(660,25, 100, 40);
        update.setText("Update");
        update.setVisible(false);
        
        p0.add(lbl3);
        lbl3.setBounds(750, 7, 100, 25);
        lbl3.setText("Total Client");
        lbl3.setForeground(Color.BLUE);
        lbl3.setFont(new Font("Monotype Corsiva",Font.ITALIC,20));
        
         p0.add(lbl4);
        lbl4.setBounds(750, 32, 100, 40);
        lbl4.setForeground(Color.MAGENTA);
        lbl4.setFont(new Font("Monotype Corsiva",Font.ITALIC,20));
        lbl4.setHorizontalAlignment(JLabel.CENTER);
       
        /* Table Edit and UPdate Display setting Data and code */
      
        
        pane.add(scp);
        scp.setBounds(360, 140, 510, 270);
        scp.setVisible(false);
        
        scp.setViewportView(table);
	table.setBounds(0, 0, 480, 400);
	scp.setBackground(Color.BLUE);
        
        
        
        /*Total Count Query*/
        
        try {
			String sql1 = "SELECT Count(Name)as total from chain.registration;";
			DBManager db = null;
                        Connection connection = db.openDBConnection();
			PreparedStatement ps1 =connection.prepareStatement(sql1);
			ResultSet rs1 = ps1.executeQuery();
			while (rs1.next()) {
				String count = rs1.getString("total");
				lbl4.setText(count);
			}

			

		} catch (Exception e5) {
			//JOptionPane.showMessageDialog(null, "Your database problem");

		}
        
        clear.addActionListener(new ActionListener(){

            @Override
            public void actionPerformed(ActionEvent e) {
                idtxt.setText("");
                nametxt.setText("");
                citytxt.setText("");
                satettxt.setText("");
                pintxt.setText("");
                phtxt.setText("");
                vehtxt.setText("");
                gifttxt.setText("");
            }
            
        });
        update.addActionListener(new ActionListener() {

            @Override
            public void actionPerformed(ActionEvent e) {
               try{
                   String emid=idtxt.getText();
                   System.out.println("The empliopyee"+emid);
                           
                   String name=nametxt.getText();
                   String vech=vehtxt.getText();
                   String phn=phtxt.getText();
                   String gift=gifttxt.getText();
                   StringBuffer addr=new StringBuffer();
                       addr.append(citytxt.getText());
                       addr.append(",");
                       addr.append(satettxt.getText());
                       addr.append(",");
                       addr.append(pintxt.getText());
                   System.out.println(addr);
                  String sql11="UPDATE chain.registration SET name='"+name+"', vehc_reg= '"+vech+"', phone_no= '"+phn+"', gift='"+gift+"', addrass='"+addr+"' WHERE empid='"+emid+"';";
                  System.out.println(sql11); 
                  DBManager db=null;
                   Connection con=db.openDBConnection();
                   Statement st = con.createStatement();
                   System.out.println("Update Row No"+st.executeUpdate(sql11));
                   
               }catch(Exception e1){
                   e1.printStackTrace();
               }
               update.setVisible(false);
                
            }
        });
        
        exit.addActionListener(new ActionListener(){

            @Override
            public void actionPerformed(ActionEvent e) {
                frame.dispose();
              new ChainRegistration();
            }
            
        });
        
        edit.addActionListener(new ActionListener(){

            @Override
            public void actionPerformed(ActionEvent e) {
              update.setVisible(true);
              scp.setVisible(true);
          
              try{
                String sql="SELECT EMPID,NAME FROM CHAIN.REGISTRATION";  
                DBManager db = null;
                Connection connection = db.openDBConnection();
                PreparedStatement ps1 = (PreparedStatement)connection.prepareStatement(sql);
	        ResultSet rs1 = ps1.executeQuery();
                table.setModel(DbUtils.resultSetToTableModel(rs1));
                lbid.setVisible(false);
                idtxt.setVisible(false);
                
                  
              }
              catch(Exception sqle){
                  
                
                  
              }
            }
            
        });
        /*Table Edit Add Mouse Click*/
        table.addMouseListener(new MouseAdapter() {
       public void mouseClicked(MouseEvent e) {
           try{
               System.out.println("hi");
               int row = table.getSelectedRow();
	       String tb = (table.getModel().getValueAt(row, 0).toString());
	       String sql = "Select * from CHAIN.registration where EMPID='"+ tb + "';";
               DBManager db=null;
               Connection con=db.openDBConnection();
             PreparedStatement ps = (PreparedStatement) con.prepareStatement(sql);
	     ResultSet rs = ps.executeQuery();
             while (rs.next()) {
              String emp = rs.getString(1);
	     idtxt.setText(emp);
	     String name = rs.getString(2);
	     nametxt.setText(name);
              String add=rs.getString(6);
              String[] parts = add.split(",");
              String city = parts[0];
              System.out.println(city);
              citytxt.setText(city);
              String state=parts[1];
              satettxt.setText(state);
              String pin=parts[2];
              pintxt.setText(pin);
              String cont=rs.getString(4);
              phtxt.setText(cont);
              String vehc=rs.getString(3);
              vehtxt.setText(vehc);
              String gift=rs.getString(5);
              gifttxt.setText(gift);
								
              }     
           }
           catch(Exception e1){
               e1.printStackTrace();
           }
       
       
       }
       
        });
        save.addMouseListener(new MouseAdapter() {
         public void mouseClicked(MouseEvent e) {
         saving1();
         frame.dispose();
         String empid=idtxt.getText();
                try {
                    new ClientInfo(empid);
                } catch (Exception ex) {
                    Logger.getLogger(ChainRegistration.class.getName()).log(Level.SEVERE, null, ex);
                }
         
         
         }
        
        });
        clint.addMouseListener(new MouseAdapter() {
        
          public void mouseClicked(MouseEvent e) {
                try {
                    new ClientDetails();
                    frame.dispose();
                } catch (Exception ex) {
                    Logger.getLogger(ChainRegistration.class.getName()).log(Level.SEVERE, null, ex);
                }
          }
        
        });
        
        

    }
    public void  saving1(){
            String empid=idtxt.getText();
            String name=nametxt.getText();
            StringBuffer add=new StringBuffer();
            add.append(citytxt.getText());
            add.append(",");
            add.append(satettxt.getText());
            add.append(",");
            add.append(pintxt.getText());
            System.out.println(add);
            String cont=phtxt.getText();
            String vehc=vehtxt.getText();
            String gift=gifttxt.getText();
            
        
            try{
               String sql = "INSERT INTO CHAIN.REGISTRATION(EMPID,NAME,VEHC_REG,PHONE_NO,GIFT,ADDRASS) VALUES ('"+empid+"', '"+name+"','"+vehc+"','"+cont+"','"+gift+"','"+add+"');";
               DBManager db = null;
               Connection connection = db.openDBConnection();
               PreparedStatement ps = connection.prepareStatement(sql);
               int n=ps.executeUpdate();
              System.out.println("No update is:"+n);
            }catch(Exception e){
                e.printStackTrace();;
            }
        }

    public static void main(String args[]) {
        new ChainRegistration();
    }
}
